const { pool } = require('../config/db');

class Adoption {
  // Create adoption application
  static async createApplication(applicationData) {
    const {
      user_id,
      pet_id,
      reason_for_adoption,
      living_situation,
      previous_pet_experience,
      additional_notes
    } = applicationData;

    try {
      // Check if user already has a pending application for this pet
      const [existing] = await pool.query(
        'SELECT * FROM adoption_applications WHERE user_id = ? AND pet_id = ? AND status = "pending"',
        [user_id, pet_id]
      );

      if (existing.length > 0) {
        throw new Error('You already have a pending application for this pet');
      }

      // 设置当前时间为申请时间
      const currentTime = new Date().toISOString().slice(0, 19).replace('T', ' ');

      const [result] = await pool.query(
        `INSERT INTO adoption_applications 
          (user_id, pet_id, reason_for_adoption, living_situation, previous_pet_experience, additional_notes, application_date, status)
        VALUES (?, ?, ?, ?, ?, ?, ?, 'pending')`,
        [user_id, pet_id, reason_for_adoption, living_situation, previous_pet_experience || null, additional_notes || null, currentTime]
      );

      return this.getApplicationById(result.insertId);
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // Get adoption application by ID
  static async getApplicationById(id) {
    try {
      const [rows] = await pool.query(
        `SELECT aa.id, 
                aa.user_id, 
                aa.pet_id, 
                aa.status, 
                COALESCE(aa.application_date, NOW()) as application_date, 
                aa.approval_date, 
                aa.reason_for_adoption, 
                aa.living_situation, 
                aa.previous_pet_experience, 
                aa.additional_notes,
                p.name as pet_name, 
                p.breed as pet_breed,
                p.gender as pet_gender,
                p.age as pet_age,
                p.image_url as pet_image,
                u.username as applicant_username,
                u.full_name as applicant_name,
                u.email as applicant_email,
                u.phone as applicant_phone,
                u.address as applicant_address
         FROM adoption_applications aa
         LEFT JOIN pets p ON aa.pet_id = p.id
         LEFT JOIN users u ON aa.user_id = u.id
         WHERE aa.id = ?`,
        [id]
      );
      
      if (rows.length === 0) {
        return null;
      }
      
      const row = rows[0];
      
      // 获取当前时间作为默认时间
      const now = new Date().toISOString();
      
      // 格式化结果为前端期望的结构
      return {
        id: row.id,
        user_id: row.user_id,
        pet_id: row.pet_id,
        status: row.status || 'pending',
        created_at: row.application_date ? row.application_date : now,
        updated_at: row.approval_date || row.application_date || now,
        reason_for_adoption: row.reason_for_adoption,
        living_situation: row.living_situation,
        previous_pet_experience: row.previous_pet_experience,
        additional_notes: row.additional_notes,
        // 用户信息
        name: row.applicant_name || '未知用户',
        username: row.applicant_username || '未知用户名',
        email: row.applicant_email || '',
        phone: row.applicant_phone || '',
        address: row.applicant_address || '',
        // 宠物信息作为嵌套对象
        pet: {
          id: row.pet_id,
          name: row.pet_name || '未知宠物',
          breed: row.pet_breed || '未知品种',
          gender: row.pet_gender || 'unknown',
          age: row.pet_age || 0,
          image_url: row.pet_image || null
        }
      };
    } catch (error) {
      console.error('获取申请详情失败');
      throw new Error(error.message);
    }
  }

  // Get all applications by user ID
  static async getApplicationsByUser(userId) {
    try {
      // 使用LEFT JOIN而不是JOIN，这样即使宠物记录不存在也能获取申请记录
      const [rows] = await pool.query(
        `SELECT aa.id, 
                aa.user_id, 
                aa.pet_id, 
                aa.status, 
                COALESCE(aa.application_date, NOW()) as application_date, 
                aa.approval_date, 
                aa.reason_for_adoption, 
                aa.living_situation, 
                aa.previous_pet_experience, 
                aa.additional_notes,
                p.name as pet_name, 
                p.breed as pet_breed,
                p.gender as pet_gender,
                p.age as pet_age,
                p.image_url as pet_image
         FROM adoption_applications aa
         LEFT JOIN pets p ON aa.pet_id = p.id
         WHERE aa.user_id = ?
         ORDER BY COALESCE(aa.application_date, NOW()) DESC`,
        [userId]
      );
      
      // 获取当前时间作为默认时间
      const now = new Date().toISOString();
      
      // 格式化结果，使其符合前端期望的结构
      return rows.map(row => {
        return {
          id: row.id,
          user_id: row.user_id,
          pet_id: row.pet_id,
          status: row.status || 'pending',
          // 确保时间字段总是有值
          created_at: row.application_date ? row.application_date : now,
          updated_at: row.approval_date || row.application_date || now,
          reason_for_adoption: row.reason_for_adoption,
          living_situation: row.living_situation,
          previous_pet_experience: row.previous_pet_experience,
          additional_notes: row.additional_notes,
          // 将宠物信息格式化为嵌套对象，即使宠物可能不存在
          pet: {
            id: row.pet_id,
            name: row.pet_name || '未知宠物',
            breed: row.pet_breed || '未知品种',
            gender: row.pet_gender || 'unknown',
            age: row.pet_age || 0,
            image_url: row.pet_image || null
          }
        };
      });
    } catch (error) {
      console.error('获取用户申请列表失败');
      throw new Error(error.message);
    }
  }

  // Get all applications for a pet
  static async getApplicationsByPet(petId) {
    try {
      const [rows] = await pool.query(
        `SELECT aa.*, 
                u.username as applicant_username,
                u.full_name as applicant_name,
                u.email as applicant_email,
                u.phone as applicant_phone
         FROM adoption_applications aa
         JOIN users u ON aa.user_id = u.id
         WHERE aa.pet_id = ?
         ORDER BY aa.application_date DESC`,
        [petId]
      );
      return rows;
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // Get application count for a pet
  static async getApplicationsCountByPet(petId) {
    try {
      const [rows] = await pool.query(
        'SELECT COUNT(*) as count FROM adoption_applications WHERE pet_id = ?',
        [petId]
      );
      return rows[0].count;
    } catch (error) {
      console.error('获取宠物申请数量失败');
      throw new Error(error.message);
    }
  }

  // Get all applications (admin)
  static async getAllApplications(status = null, page = 1, limit = 10) {
    const offset = (page - 1) * limit;
    let query = `
      SELECT aa.*, 
             p.name as pet_name, 
             p.image_url as pet_image,
             u.username as applicant_username,
             u.full_name as applicant_name
      FROM adoption_applications aa
      JOIN pets p ON aa.pet_id = p.id
      JOIN users u ON aa.user_id = u.id
    `;
    
    const queryParams = [];
    
    if (status) {
      query += ' WHERE aa.status = ?';
      queryParams.push(status);
    }
    
    query += ' ORDER BY aa.application_date DESC LIMIT ? OFFSET ?';
    queryParams.push(parseInt(limit), offset);
    
    try {
      const [rows] = await pool.query(query, queryParams);
      
      // Get total count for pagination
      let countQuery = 'SELECT COUNT(*) as total FROM adoption_applications';
      if (status) {
        countQuery += ' WHERE status = ?';
      }
      
      const [countResult] = await pool.query(
        countQuery,
        status ? [status] : []
      );
      
      return {
        applications: rows,
        pagination: {
          total: countResult[0].total,
          page: parseInt(page),
          limit: parseInt(limit),
          totalPages: Math.ceil(countResult[0].total / limit)
        }
      };
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // Update application status
  static async updateApplicationStatus(id, status, userId = null) {
    try {
      if (status === 'approved') {
        // If approving, update approval_date
        await pool.query(
          'UPDATE adoption_applications SET status = ?, approval_date = NOW() WHERE id = ?',
          [status, id]
        );
        
        // Get pet_id from the application
        const [appRows] = await pool.query(
          'SELECT pet_id FROM adoption_applications WHERE id = ?',
          [id]
        );
        
        if (appRows.length > 0) {
          // Mark pet as adopted
          await pool.query(
            'UPDATE pets SET is_adopted = TRUE WHERE id = ?',
            [appRows[0].pet_id]
          );
          
          // Create adoption record
          if (userId) {
            await pool.query(
              'INSERT INTO adoption_records (application_id, adoption_fee) VALUES (?, 0)',
              [id]
            );
          }
        }
      } else {
        // Just update status
        await pool.query(
          'UPDATE adoption_applications SET status = ? WHERE id = ?',
          [status, id]
        );
      }
      
      return this.getApplicationById(id);
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // Get adoption records
  static async getAdoptionRecords(page = 1, limit = 10) {
    const offset = (page - 1) * limit;
    
    try {
      const [rows] = await pool.query(
        `SELECT ar.*, 
                aa.user_id, aa.pet_id,
                p.name as pet_name, 
                p.image_url as pet_image,
                u.username as adopter_username,
                u.full_name as adopter_name
         FROM adoption_records ar
         JOIN adoption_applications aa ON ar.application_id = aa.id
         JOIN pets p ON aa.pet_id = p.id
         JOIN users u ON aa.user_id = u.id
         ORDER BY ar.adoption_date DESC
         LIMIT ? OFFSET ?`,
        [parseInt(limit), offset]
      );
      
      // Get total count for pagination
      const [countResult] = await pool.query(
        'SELECT COUNT(*) as total FROM adoption_records'
      );
      
      return {
        records: rows,
        pagination: {
          total: countResult[0].total,
          page: parseInt(page),
          limit: parseInt(limit),
          totalPages: Math.ceil(countResult[0].total / limit)
        }
      };
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // Get user's adopted pets
  static async getUserAdoptions(userId) {
    try {
      const [rows] = await pool.query(
        `SELECT ar.*, 
                p.id as pet_id,
                p.name as pet_name, 
                p.image_url as pet_image,
                p.breed, p.age, p.gender
         FROM adoption_records ar
         JOIN adoption_applications aa ON ar.application_id = aa.id
         JOIN pets p ON aa.pet_id = p.id
         WHERE aa.user_id = ?
         ORDER BY ar.adoption_date DESC`,
        [userId]
      );
      return rows;
    } catch (error) {
      throw new Error(error.message);
    }
  }
}

module.exports = Adoption; 